/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cn.jgls.bi.dataprocess;

import java.util.List;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.RowMapperResultReader;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import java.sql.Date;
import java.util.ArrayList;
import java.util.Iterator;

/**
 *
 * @author zz
 */
public class DataProcessJdbcDao extends JdbcDaoSupport implements DataProcessDao {

    public List getFunction() {
        JdbcTemplate jt = getJdbcTemplate();
        return jt.query("select name from t_bi_function_list where bj=1",
                new RowMapperResultReader(new getFunctionRM()));

    }

    public void TsnAmt(Date adTsnDate, int aiShopId, String asShopBm,
            int aiDateNo, String asYear, int aiYybh, int aiMan_id,String asTname) {
        //开始处理
        int TsnAmt;
        int iRc;
        int aiYear;
        int TsnSort;
        int FrtAmt;
        int VegAmt;
        int iWeekAmt=0,iYearAmt=0;
        int iWeekFrt=0,iYearFrt=0,iWeekVeg=0,iYearVeg=0;
        String sql="";
        float fWeekAddup=0,fYearAddup=0;
        float fWeekAddupFrt=0,fYearAddupFrt=0;
        float fWeekAddupVeg=0,fYearAddupVeg=0;
        List lstWeek,lstYear;
        Date ldYesterday;
        System.out.println("now processing TsnAmt's Data... Date:" + adTsnDate +
                " shopid;" + aiShopId + " please wait...");
        aiYear = Integer.parseInt(asYear);
        JdbcTemplate jt = getJdbcTemplate();
        sql = "select count(distinct(jqh||empl_id||trnno)) from " + asTname +
                    " where saledate = ? and bm=? and trnno<>9999 ";
        Object[] params = new Object[]{adTsnDate, asShopBm};
        int[] types = {Types.DATE, Types.VARCHAR};
        TsnAmt = jt.queryForInt(sql, params, types);
        if (TsnAmt <= 400) {
            TsnSort = 1;
        } else if (TsnAmt > 400 && TsnAmt <= 800) {
            TsnSort = 2;
        } else if (TsnAmt > 800 && TsnAmt <= 1200) {
            TsnSort = 3;
        } else if (TsnAmt > 1200 && TsnAmt <= 1600) {
            TsnSort = 4;
        } else if (TsnAmt > 1600 && TsnAmt <= 2000) {
            TsnSort = 5;
        } else {
            TsnSort = 6;
        }
        //取蔬菜交易笔数
        sql = "select count(distinct(jqh||empl_id||trnno)) from " + asTname +
            " where saledate = ? and bm=? and trnno<>9999 and lb='086000'  and sl > 0.01  ";
        VegAmt = jt.queryForInt(sql, params, types);
        //取水果交易笔数
        sql = "select count(distinct(jqh||empl_id||trnno)) from " + asTname +
            " where saledate = ? and bm=? and trnno<>9999 and lb='087000'  and sl > 0.01  ";
        FrtAmt = jt.queryForInt(sql, params, types);
        
        //取周平均累计
        sql="select week_addup,week_amt,week_frt,week_veg,week_addup_frt,week_addup_veg " +
                "from t_bi_tsnamt_mx where tsn_date = " +
                "(select max(tsn_date) from t_bi_tsnamt_mx where tsn_date < ? " +
                "and tsn_date_no=? and tsn_id=?)" +
                " and tsn_id = ? and tsn_year = ?";
        params = new Object[]{adTsnDate,aiDateNo,aiShopId,aiShopId,aiYear};
        types = new int[]{Types.DATE,Types.INTEGER,Types.INTEGER,Types.INTEGER,Types.INTEGER};
        lstWeek=jt.query(sql,params,types,new RowMapperResultReader(new TsnAmtMxWeekRM()));
        Iterator Iter = lstWeek.iterator(); 
        TsnAmtMx ltam = new TsnAmtMx();
        while(Iter.hasNext()){
            ltam = (TsnAmtMx)Iter.next();
            iWeekAmt  = ltam.getWeek_amt();
            fWeekAddup = ltam.getWeek_addup();
            iWeekFrt = ltam.getWeek_frt();
            iWeekVeg = ltam.getWeek_veg();
            fWeekAddupFrt = ltam.getWeek_addup_frt();
            fWeekAddupVeg = ltam.getWeek_addup_veg();
        }
        
        //取年平均累计
        sql="select year_addup,year_amt,year_frt,year_veg,year_addup_frt," +
                "year_addup_veg from t_bi_tsnamt_mx " +
                "where tsn_date = (? - 1) and tsn_id = ? and tsn_year=?";
        params = new Object[]{adTsnDate,aiShopId,aiYear};
        types = new int[]{Types.DATE,Types.INTEGER,Types.INTEGER};
        lstYear=jt.query(sql,params,types,new RowMapperResultReader(new TsnAmtMxYearRM()));
        Iter = lstYear.iterator(); 
        ltam = new TsnAmtMx();
        while(Iter.hasNext()){
            ltam = (TsnAmtMx)Iter.next();
            iYearAmt  = ltam.getYear_amt();
            fYearAddup = ltam.getYear_addup();
            iYearFrt = ltam.getYear_frt();
            iYearVeg = ltam.getYear_veg();
            fYearAddupFrt = ltam.getYear_addup_frt();
            fYearAddupVeg = ltam.getYear_addup_veg();
        }
        //全部
        iWeekAmt   = iWeekAmt + 1;
        fWeekAddup = fWeekAddup + TsnAmt;
        iYearAmt   = iYearAmt + 1;
        fYearAddup = fYearAddup + TsnAmt;
        //水果
        iWeekFrt   = iWeekFrt + 1;
        fWeekAddupFrt = fWeekAddupFrt + FrtAmt;
        iYearFrt   = iYearFrt + 1;
        fYearAddupFrt = fYearAddupFrt + FrtAmt;
        //蔬菜
        iWeekVeg   = iWeekVeg + 1;
        fWeekAddupVeg = fWeekAddupVeg + VegAmt;
        iYearVeg   = iYearVeg + 1;
        fYearAddupVeg = fYearAddupVeg + VegAmt;

        
        //begin insert into TsnAmt
        //1:delete
        params = new Object[]{adTsnDate, aiShopId};
        types = new int[]{Types.DATE, Types.INTEGER};
        sql = "delete from t_bi_tsnamt_mx where tsn_date = ? and tsn_id = ?";
        iRc = jt.update(sql, params, types);
        //2:insert
        sql = "insert into t_bi_tsnamt_mx " +
                "(tsn_date,tsn_id,tsn_amt,tsn_year,tsn_date_no,yybh,man_id,tsn_sort" +
                ",week_addup,week_amt,year_addup,year_amt," +
                "frt_amt,week_addup_frt,week_frt,year_addup_frt,year_frt," +
                "veg_amt,week_addup_veg,week_veg,year_addup_veg,year_veg)" +
                " values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        params = new Object[]{adTsnDate, aiShopId, TsnAmt, 
        aiYear, aiDateNo, aiYybh, aiMan_id, TsnSort,fWeekAddup,iWeekAmt,fYearAddup,iYearAmt,
        FrtAmt,fWeekAddupFrt,iWeekFrt,fYearAddupFrt,iYearFrt,
        VegAmt,fWeekAddupVeg,iWeekVeg,fYearAddupVeg,iYearVeg};
        types = new int[]{Types.DATE, Types.INTEGER, Types.INTEGER,
        Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.INTEGER
        ,Types.FLOAT,Types.INTEGER,Types.FLOAT,Types.INTEGER,
        Types.INTEGER,Types.FLOAT,Types.INTEGER,Types.FLOAT,Types.INTEGER,
        Types.INTEGER,Types.FLOAT,Types.INTEGER,Types.FLOAT,Types.INTEGER};
        iRc = jt.update(sql, params, types);
        
    }

    @Override
    public List getShopList(Date adTsnDate,int aiBj) {
        List lTmp;
        List<ArchiDept> lTmp2;
        ArchiDept lad = new ArchiDept();
        JdbcTemplate jt = getJdbcTemplate();
        String sql = "";
        if(aiBj==0){
            sql="select id,bm,mc,pz,man_id,yybh,area from sy_archi_dept where ckbj=1  order by id";
            lTmp=jt.query(sql,new RowMapperResultReader(new ArchiDeptRM()));
            
        }else{
            sql="select id,bm,mc,pz,man_id,yybh,area from sy_archi_dept where id in (select tsn_id from t_bi_shoplist_noupload where tsn_date = ?)  order by id";
            Object[] params = new Object[]{adTsnDate};
            int[] types = new int[]{Types.DATE};
            lTmp=jt.query(sql,params,types,new RowMapperResultReader(new ArchiDeptRM()));
        }
        lTmp2 = new ArrayList<ArchiDept>();
        Iterator Iter = lTmp.iterator();
        while(Iter.hasNext()){
            lad=(ArchiDept)Iter.next();
            lTmp2.add(lad);
        }
        return lTmp2;
        
    }
    //定义供jdbctemplete使用的内部类
    class getFunctionRM implements RowMapper {

        @Override
        public Object mapRow(ResultSet rs, int index) throws SQLException {
            return rs.getString(1);
        }
    }

    class ArchiDeptRM implements RowMapper {

        @Override
        public Object mapRow(ResultSet rs, int index)
                throws SQLException {
            ArchiDept ad = new ArchiDept();
            ad.setId(new Integer(rs.getInt("id")));
            ad.setBm(rs.getString("bm"));
            ad.setMc(rs.getString("mc"));
            ad.setPz(new Integer(rs.getInt("pz")));
            ad.setMan_id(new Integer(rs.getInt("man_id")));
            ad.setYybh(new Integer(rs.getInt("yybh")));
            ad.setArea(new Float(rs.getFloat("area")));
            return ad;
        }
    }

    class TsnAmtMxYearRM implements RowMapper {

        @Override
        public Object mapRow(ResultSet rs, int index)
                throws SQLException {
            TsnAmtMx wavg = new TsnAmtMx();
            wavg.setYear_addup(new Float(rs.getFloat("year_addup")));
            wavg.setYear_amt(new Integer(rs.getInt("year_amt")));
            wavg.setYear_frt(new Integer(rs.getInt("year_frt")));
            wavg.setYear_addup_frt(new Float(rs.getFloat("year_addup_frt")));
            wavg.setYear_veg(new Integer(rs.getInt("year_veg")));
            wavg.setYear_addup_veg(new Float(rs.getFloat("year_addup_veg")));
            return wavg;
        }
    }
    class TsnAmtMxWeekRM implements RowMapper {

        @Override
        public Object mapRow(ResultSet rs, int index)
                throws SQLException {
            TsnAmtMx wavg = new TsnAmtMx();
            wavg.setWeek_addup(new Float(rs.getFloat("week_addup")));
            wavg.setWeek_amt(new Integer(rs.getInt("week_amt")));
            wavg.setWeek_frt(new Integer(rs.getInt("week_frt")));
            wavg.setWeek_addup_frt(new Float(rs.getFloat("week_addup_frt")));
            wavg.setWeek_veg(new Integer(rs.getInt("week_veg")));
            wavg.setWeek_addup_veg(new Float(rs.getFloat("week_addup_veg")));
            return wavg;
        }
    }
    
}
